﻿/*
	Date	: 10.10.11
	Author	: Jarryd
	Purpose	: Relocate Seat Functionality.
	
 */
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;


namespace GoldAir_DesktopApp
{
    public partial class frmRealocate : Form
    {
        public frmRealocate()
        {
            InitializeComponent();
        }

        //Lookup flight function
        private static DataTable LookupFlight(int UserId, int FlightNumber)
        {

            const string connStr = "server=localhost;User Id=goldair_dapp;password=geckodev;database=goldair_db";
            const string query = "Select route.from,route.destination,flight.departure,user_flight.num_seat,user_flight.checked_in,user_flight.checkin_datetime,user_flight.payment,user_flight.payment_date,user_flight.confirmed  from user_flight join flight on user_flight.flight_id=flight.id join route on flight.route_id=route.id Where user_id = @UserId and flight_id = @FlightNumber";

            MySqlConnection connection = new MySqlConnection();
            MySqlDataAdapter data = new MySqlDataAdapter();

            connection.ConnectionString = connStr;
            connection.Open();
            MySqlCommand command = connection.CreateCommand();


            DataTable result = new DataTable();
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(query, conn))
                {
                    cmd.Parameters.Add("@UserId", MySqlDbType.Int32).Value = UserId;
                    cmd.Parameters.Add("@FlightNumber", MySqlDbType.Int32).Value = FlightNumber;

                    using (MySqlDataReader dr = cmd.ExecuteReader())
                    {
                        result.Load(dr);
                    }
                }
                conn.Close();
            }

            connection.Close();

            return result;
        }

        private void ClearForm()
        {

            txtFrom.Text = "";
            txtTo.Text = "";
            txtFlightTime.Text = "";
            txtCurrentSeat.Text = "";            
            txtStatus.Text = "";
            cmbNewSeat.Items.Clear();
            txtUserId.Focus();

        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (txtFlightNumber.Text != "" && txtUserId.Text != "")
            {

                using (DataTable dt = LookupFlight(Int32.Parse(txtUserId.Text), Int32.Parse(txtFlightNumber.Text)))
                {
                    if (dt.Rows.Count == 0)
                    {
                        txtUserId.Focus();
                        MessageBox.Show("Not Found.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);

                        ClearForm();
                        return;
                    }
                    else
                    {
                        //assign the data to the controls
                        txtFrom.Text = Convert.ToString(dt.Rows[0][0]);  //from
                        txtTo.Text = Convert.ToString(dt.Rows[0][1]); //to
                        txtFlightTime.Text = Convert.ToString(dt.Rows[0][2]); //flight time
                        txtCurrentSeat.Text = Convert.ToString(dt.Rows[0][3]); //seat n
                                                
                        switch (Convert.ToString(dt.Rows[0][8]))
                        {
                            case "0":
                                txtStatus.Text = "UNCHECKED";
                                break;
                            case "1":
                                txtStatus.Text = "CHECKED IN";
                                break;
                            case "2":
                                txtStatus.Text = "PAYD";
                                break;
                        }
                        //Look for the fly/aircraft and available seat numbers

                        Fill_Seats(Int32.Parse(txtFlightNumber.Text));


                    }
                }
            }
            else
            {
                MessageBox.Show("Enter User Id and Flight Id", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                ClearForm();
            }
        }



        private void Fill_Seats(Int32 FlightNumber)
        {

            String[] Seats = { "1A", "2A", "3A", "3B", "3C", "4A", "4B", "4C", "5A", "5B", "5C", "6A", "6B", "6C", "7A", "7B", "7C", "8A", "8B", "8C", "9A", "9B", "9C", "10A", "10B", "10C", "11A", "11B", "11C", "12A", "12B", "12C", "13A", "13B", "13C", "14A", "14B", "14C", "15A", "15B", "15C", "16A", "16B", "16C", "17A", "17B", "17C", "18A", "18B", "18C" };

            const string connStr = "server=localhost;User Id=goldair_dapp;password=geckodev;database=goldair_db";
            const string query = "Select aircraft.accomodation,user_flight.num_seat from user_flight join flight on user_flight.flight_id=flight.id join aircraft on flight.aircraft_id=aircraft.id Where flight_id = @FlightNumber and user_flight.num_seat <> 0 order by user_flight.num_seat";

            cmbNewSeat.Items.Clear();
            Int32 NumSeats = 0;

            MySqlConnection connection = new MySqlConnection();
            MySqlDataAdapter data = new MySqlDataAdapter();

            connection.ConnectionString = connStr;
            connection.Open();
            
            MySqlCommand command = connection.CreateCommand();

            DataTable result = new DataTable();
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(query, conn))
                {
                    cmd.Parameters.Add("@FlightNumber", MySqlDbType.Int32).Value = FlightNumber;

                    using (MySqlDataReader dr = cmd.ExecuteReader())
                    {
                        result.Load(dr);

                        try
                        {

                            NumSeats = Convert.ToInt16(result.Rows[0][0]);
                        }
                        catch
                        {
                            NumSeats = 48;
                        }

                        NumSeats = Seats.Length;

                        //MessageBox.Show(NumSeats.ToString());
                        for (int i = 0; i < NumSeats; i++)
                        {
                            int flag = 0;
                            foreach (DataRow row in result.Rows)
                            {
                                if (Seats[i].Equals(row[1])) flag = 1;
                            }
                            
                            if (flag==0) cmbNewSeat.Items.Add(Seats[i]);                            
                        }
                                                
                    }
                }
                conn.Close();
            }

            connection.Close();
        
        
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (txtFrom.Text != "")
            {

                String Seat = cmbNewSeat.Text;


                const string connStr = "server=localhost;User Id=goldair_dapp;password=geckodev;database=goldair_db";
                const string query = "Update user_flight set num_seat= @Seat Where user_id = @UserId and flight_id = @FlightNumber";

                MySqlConnection connection = new MySqlConnection();
                MySqlDataAdapter data = new MySqlDataAdapter();

                connection.ConnectionString = connStr;
                connection.Open();
                MySqlCommand command = connection.CreateCommand();

                using (MySqlConnection conn = new MySqlConnection(connStr))
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(query, conn))
                    {
                        cmd.Parameters.Add("@UserId", MySqlDbType.Int32).Value = txtUserId.Text;
                        cmd.Parameters.Add("@FlightNumber", MySqlDbType.Int32).Value = txtFlightNumber.Text;                        
                        cmd.Parameters.Add("@Seat", MySqlDbType.String).Value = Seat;

                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
                MessageBox.Show("Seat Reallocated!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                connection.Close();

                btnSearch.PerformClick();
            }
            else
            {
                MessageBox.Show("No Data Found", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }


    }
}
